package utils;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class GenerateData 
{
	private static Connection C;
	private static Statement Stmt;
	
	public static void main(String [ ] args) throws SQLException, IOException
	{
		StringBuffer attributes = new StringBuffer("@relation normscase\n\n");
		StringBuffer data = new StringBuffer("@data\n");
		StringBuffer comment = new StringBuffer("%");
		ArrayList<String> norms = new ArrayList<String>();
		
		
		C = connectDB();
		
		ArrayList ids = new ArrayList();
		Statement select = C.createStatement();
		ResultSet result = select.executeQuery("SELECT DISTINCT caseid FROM umCourtl.normscase");
		
		while (result.next())
			ids.add(result.getString(1));
		
		//first sweep, get all articles, norms and items addressed
		for (int i=0;i<ids.size();i++)
		{
			select = C.createStatement();
			result = select.executeQuery("SELECT DISTINCT article, number, item FROM umCourtl.norms");
			
			while (result.next())
			{
				int article = result.getInt(1);
				int number = result.getInt(2);
				String item = result.getString(3);
				
				String norm = new String();
				
				if (number == 0)
					norm = article+"";
				else
					if (item == null)
						norm = article+"-"+number+"";
					else
						norm = article+"-"+number+"-"+item;
				
				if (!norms.contains(norm))
					norms.add(norm);
			}
		}

		attributes.append("\n@attribute caseid {");
		for (int i=0;i<ids.size();i++)
		{
			attributes.append(ids.get(i));
			if (i+1 == ids.size())
				attributes.append("}\n");
			else
				attributes.append(",");
		}
		
		for(int i=0;i<norms.size();i++)
		{
			attributes.append("@attribute norms"+i+" {0,1}\n");
			comment.append("norms"+i+" - Art."+norms.get(i)+", ");
		}
		
		attributes.append("@attribute party1wins {0,1}\n");
		
		comment.append("\n\n");
		
		//for each case, get the norms adressed and add the corresponding true or false values to the file
		for (int i=0;i<ids.size();i++)
		{
			select = C.createStatement();
			result = select.executeQuery("SELECT DISTINCT article, number, item FROM umCourtl.normscase WHERE caseid = '"+ids.get(i)+"'");
			ArrayList<String> normsAddressed = new ArrayList<String>();
			
			while (result.next())
			{
				int article = result.getInt(1);
				int number = result.getInt(2);
				String item = result.getString(3);
				String norm = new String();
				
				if (number+"" == "null")
					norm = article+"";
				else
					if (item.equals("null"))
						norm = article+"-"+number+"";
					else
						norm = article+"-"+number+"-"+item;
				normsAddressed.add(norm);
			}
			
			data.append(ids.get(i)+",");
			for (int j=0;j<norms.size();j++)
			{
				if (normsAddressed.contains(norms.get(j)))
					data.append("1");
				else
					data.append("0");
				if(j != norms.size()-1)
					data.append(",");
				else
				{
					select = C.createStatement();
					result = select.executeQuery("SELECT party1wins FROM umCourtl.casebase WHERE caseid = '"+ids.get(i)+"'");
					result.next();
					int party1wins = result.getInt(1);
					data.append(","+party1wins+"\n");
				}
			}
		}
			
		//attributes.append("}\n@attribute norm NUMERIC\n@attribute domain {Rights,Obligations,Mix}\n\n");
		
		FileWriter fstream = new FileWriter("data.arff");
        BufferedWriter out = new BufferedWriter(fstream);
	    out.write(attributes.toString()+comment.toString()+data.toString());
	    out.close();
	}
	
	private static Connection connectDB()
	{
		//Connect to Database
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
	    catch (Exception E) 
	    {
	    	System.err.println("Unable to load driver");
	        E.printStackTrace();
	    }

	    try 
	    {
	    	C = DriverManager.getConnection("jdbc:mysql://tiaracserver.di.uminho.pt:3306","dave","d3LLdav#");
	    }
	    catch (SQLException E) 
	    {
	        System.out.println("SQLException: " + E.getMessage());
	        System.out.println("SQLState:     " + E.getSQLState());
	        System.out.println("VendorError:  " + E.getErrorCode());
	    }
	    
	    return C;
	}
}
